const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

console.log('🔍 验证87个实例表的整合式结构更新结果');
console.log('=========================================');

try {
  const db = new Database(DB_PATH);
  
  console.log('\n📊 获取所有实例表信息...');
  
  // 获取所有实例表
  const allInstanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`📋 实例表总数: ${allInstanceTables.length} 个`);
  
  // 详细分析每个表的结构
  const tableAnalysis = [];
  let totalFields = 0;
  let totalNamingFields = 0;
  let totalPositionFields = 0;
  let totalBaseParamsFields = 0;
  let totalRecords = 0;
  
  console.log('\n🔍 分析每个表的字段结构...');
  console.log('=' * 70);
  
  for (const table of allInstanceTables) {
    const tableName = table.name;
    
    try {
      // 获取表结构信息
      const tableInfo = db.prepare(`PRAGMA table_info([${tableName}])`).all();
      const fieldNames = tableInfo.map(f => f.name);
      
      // 分类统计字段
      const namingFields = fieldNames.filter(f => f.startsWith('naming_'));
      const positionFields = fieldNames.filter(f => f.startsWith('position_'));
      const baseParamsFields = fieldNames.filter(f => f.startsWith('base_params_'));
      const systemFields = fieldNames.filter(f => ['id', 'device_type_code', 'instance_number', 'object_number', 'object_description', 'created_at', 'updated_at'].includes(f));
      
      // 获取数据记录数量
      const recordCount = db.prepare(`SELECT COUNT(*) as count FROM [${tableName}]`).get().count;
      
      const analysis = {
        tableName,
        totalFields: tableInfo.length,
        systemFields: systemFields.length,
        namingFields: namingFields.length,
        positionFields: positionFields.length,
        baseParamsFields: baseParamsFields.length,
        recordCount,
        hasIntegratedStructure: namingFields.length > 0 && (positionFields.length > 0 || baseParamsFields.length > 0)
      };
      
      tableAnalysis.push(analysis);
      
      // 累计统计
      totalFields += analysis.totalFields;
      totalNamingFields += analysis.namingFields;
      totalPositionFields += analysis.positionFields;
      totalBaseParamsFields += analysis.baseParamsFields;
      totalRecords += analysis.recordCount;
      
      // 显示详细信息
      const statusIcon = analysis.hasIntegratedStructure ? '✅' : '❌';
      console.log(`${statusIcon} ${tableName}:`);
      console.log(`    📊 总字段: ${analysis.totalFields} | 系统: ${analysis.systemFields} | 命名: ${analysis.namingFields} | 位置: ${analysis.positionFields} | 参数: ${analysis.baseParamsFields} | 记录: ${analysis.recordCount}`);
      
      if (analysis.recordCount > 0) {
        console.log(`    💾 包含 ${analysis.recordCount} 条数据记录`);
      }
      
    } catch (error) {
      console.log(`❌ 分析表 ${tableName} 失败: ${error.message}`);
      tableAnalysis.push({
        tableName,
        error: error.message,
        hasIntegratedStructure: false
      });
    }
  }
  
  // 生成统计报告
  console.log('\n📊 整合式结构验证报告');
  console.log('=========================');
  
  const integratedTables = tableAnalysis.filter(t => t.hasIntegratedStructure);
  const nonIntegratedTables = tableAnalysis.filter(t => !t.hasIntegratedStructure && !t.error);
  const errorTables = tableAnalysis.filter(t => t.error);
  
  console.log(`✅ 成功整合的表: ${integratedTables.length} 个`);
  console.log(`❌ 未整合的表: ${nonIntegratedTables.length} 个`);
  console.log(`🚨 错误的表: ${errorTables.length} 个`);
  
  // 字段统计
  console.log(`\n📊 字段统计汇总:`);
  console.log(`  总字段数: ${totalFields} 个`);
  console.log(`  命名字段: ${totalNamingFields} 个`);
  console.log(`  位置字段: ${totalPositionFields} 个`);
  console.log(`  参数字段: ${totalBaseParamsFields} 个`);
  console.log(`  数据记录: ${totalRecords} 条`);
  
  // 按专业分类统计
  console.log(`\n📊 按专业分类统计:`);
  const specialtyStats = {};
  
  for (const table of tableAnalysis) {
    if (table.error) continue;
    
    const specialty = table.tableName.split('_')[2]; // device_instances_专业_设备
    if (!specialtyStats[specialty]) {
      specialtyStats[specialty] = {
        count: 0,
        integratedCount: 0,
        totalFields: 0,
        totalRecords: 0
      };
    }
    
    specialtyStats[specialty].count++;
    if (table.hasIntegratedStructure) {
      specialtyStats[specialty].integratedCount++;
    }
    specialtyStats[specialty].totalFields += table.totalFields || 0;
    specialtyStats[specialty].totalRecords += table.recordCount || 0;
  }
  
  Object.entries(specialtyStats).forEach(([specialty, stats]) => {
    const integrationRate = ((stats.integratedCount / stats.count) * 100).toFixed(1);
    console.log(`  ${specialty}: ${stats.integratedCount}/${stats.count} 表 (${integrationRate}%) | ${stats.totalFields} 字段 | ${stats.totalRecords} 记录`);
  });
  
  // 展示字段最多和最少的表
  if (integratedTables.length > 0) {
    const sortedByFields = integratedTables.sort((a, b) => b.totalFields - a.totalFields);
    console.log(`\n📊 字段最多的表:`);
    console.log(`  ${sortedByFields[0].tableName}: ${sortedByFields[0].totalFields} 字段`);
    console.log(`  - 命名: ${sortedByFields[0].namingFields}, 位置: ${sortedByFields[0].positionFields}, 参数: ${sortedByFields[0].baseParamsFields}`);
    
    console.log(`\n📊 字段最少的表:`);
    const minFields = sortedByFields[sortedByFields.length - 1];
    console.log(`  ${minFields.tableName}: ${minFields.totalFields} 字段`);
    console.log(`  - 命名: ${minFields.namingFields}, 位置: ${minFields.positionFields}, 参数: ${minFields.baseParamsFields}`);
  }
  
  // 检查是否有数据的表
  const tablesWithData = tableAnalysis.filter(t => t.recordCount > 0);
  if (tablesWithData.length > 0) {
    console.log(`\n💾 包含数据的表 (${tablesWithData.length} 个):`);
    tablesWithData.forEach(table => {
      console.log(`  ${table.tableName}: ${table.recordCount} 条记录`);
    });
  } else {
    console.log(`\n💾 所有表都是空表，无数据记录`);
  }
  
  // 验证某个具体表的结构示例
  if (integratedTables.length > 0) {
    console.log(`\n🔍 示例表结构验证 (${integratedTables[0].tableName}):`);
    const sampleTable = integratedTables[0].tableName;
    const sampleFields = db.prepare(`PRAGMA table_info([${sampleTable}])`).all();
    
    console.log(`  📋 完整字段列表 (${sampleFields.length} 个):`);
    
    // 按类型分组显示
    const systemFields = sampleFields.filter(f => ['id', 'device_type_code', 'instance_number', 'object_number', 'object_description', 'created_at', 'updated_at'].includes(f.name));
    const namingFields = sampleFields.filter(f => f.name.startsWith('naming_'));
    const positionFields = sampleFields.filter(f => f.name.startsWith('position_'));
    const baseParamsFields = sampleFields.filter(f => f.name.startsWith('base_params_'));
    
    console.log(`    🔧 系统字段 (${systemFields.length} 个): ${systemFields.map(f => f.name).join(', ')}`);
    
    if (namingFields.length > 0) {
      console.log(`    📝 命名字段 (${namingFields.length} 个): ${namingFields.slice(0, 3).map(f => f.name).join(', ')}${namingFields.length > 3 ? '...' : ''}`);
    }
    
    if (positionFields.length > 0) {
      console.log(`    📍 位置字段 (${positionFields.length} 个): ${positionFields.slice(0, 3).map(f => f.name).join(', ')}${positionFields.length > 3 ? '...' : ''}`);
    }
    
    if (baseParamsFields.length > 0) {
      console.log(`    ⚙️  参数字段 (${baseParamsFields.length} 个): ${baseParamsFields.slice(0, 3).map(f => f.name).join(', ')}${baseParamsFields.length > 3 ? '...' : ''}`);
    }
  }
  
  // 最终结论
  console.log(`\n🎉 验证结论:`);
  const successRate = ((integratedTables.length / allInstanceTables.length) * 100).toFixed(1);
  
  if (integratedTables.length === allInstanceTables.length) {
    console.log(`✅ 完美！所有 ${allInstanceTables.length} 个实例表都已成功更新为整合式前缀命名结构`);
    console.log(`💡 每个表都包含了完整的属性字段，不再需要使用外键关联`);
    console.log(`📊 平均每个表包含 ${(totalFields / allInstanceTables.length).toFixed(1)} 个字段`);
  } else {
    console.log(`⚠️  部分成功：${integratedTables.length}/${allInstanceTables.length} 个表已整合 (${successRate}%)`);
    
    if (nonIntegratedTables.length > 0) {
      console.log(`❌ 未整合的表:`);
      nonIntegratedTables.forEach(table => {
        console.log(`  - ${table.tableName}`);
      });
    }
  }
  
  if (totalRecords > 0) {
    console.log(`💾 系统保留了 ${totalRecords} 条设备实例数据`);
  } else {
    console.log(`💾 当前系统中暂无设备实例数据（结构已准备就绪）`);
  }
  
  console.log(`\n🔧 系统现在使用统一的整合式实例表结构：`);
  console.log(`  - 每个设备实例表包含完整的设备属性信息`);
  console.log(`  - 使用前缀命名区分不同类型的属性字段`);
  console.log(`  - 无需通过外键关联多个属性表`);
  console.log(`  - 简化了查询逻辑和数据维护`);
  
  db.close();
  
} catch (error) {
  console.error('❌ 验证失败:', error.message);
  console.error(error.stack);
}